﻿using System;
using System.Collections.Generic;
using System.ComponentModel;
using System.Data.SqlClient;
using System.Data;
using System.Drawing;
using System.Linq;
using System.Text;
using System.Windows.Forms;
using CrystalDecisions.CrystalReports.Engine;
using CrystalDecisions.Shared;

namespace OfflineReport
{
    public partial class frmBaoCaoGiaiNgan : Form
    {
        SQL sql = new SQL();
        public frmBaoCaoGiaiNgan()
        {
            InitializeComponent();
        }

        private void btnshowReport_Click(object sender, EventArgs e)
        {
            crBaoCaoGiaiNgan cr = new crBaoCaoGiaiNgan();

            SqlParameter[] sqlParas = new SqlParameter[6];
            sqlParas[0] = new SqlParameter("@CURRENCY", cbLoaiTien.Text);
            sqlParas[1] = new SqlParameter("@loaiKH", cbLKH.SelectedIndex);
            sqlParas[2] = new SqlParameter("@LDLINK", cbLDLINK.Text);
            sqlParas[3] = new SqlParameter("@MaKH", cbMKH.Text);
            sqlParas[4] = new SqlParameter("@dateFrom", dateTimePickerFrom.Value.ToString("yyyy/MM/dd"));
            sqlParas[5] = new SqlParameter("@dateTo", dateTimePickerTo.Value.ToString("yyyy/MM/dd"));           
            
            DataTable table1 = sql.getDataTable("BaoCaoGiaiNgan1", sqlParas);
            System.Console.WriteLine(table1.Rows.Count);
            DataTable table2 = sql.getDataTable("BaoCaoGiaiNgan2",sqlParas);            
            DataTable table3 = sql.getDataTable("BaoCaoGiaiNgan3");
            foreach (DataRow dr in table1.Rows)
                foreach (DataRow _temp in table3.Rows) 
                {
                    if (dr["CUSTOMER.ID"].ToString() == _temp["CUSTOMER.ID"].ToString()
                        && dr["LD.LINK"].ToString() == _temp["LD.LINK"].ToString()
                        && dr["LD.ID"].ToString() == _temp["LD.ID"].ToString())
                    {
                        dr["INTEREST.RATE"] = _temp["INTEREST.RATE"];
                        dr["REPORT.DATE"] = _temp["REPORT.DATE"];
                        dr["TERM"] = _temp["TERM"];
                    }
                }

            DataTable table = union(table1, table2);
            table.DefaultView.Sort = "TERM ASC";
            table = table.DefaultView.ToTable();
            cr.SetDataSource(table);

            //definition parameters
            ParameterFieldDefinitions pfd;
            ParameterFieldDefinition crFrom;
            ParameterFieldDefinition crTo;

            ParameterValues crValues = new ParameterValues();
            ParameterDiscreteValue crDisValueFrom = new ParameterDiscreteValue();
            ParameterDiscreteValue crDisValueTo = new ParameterDiscreteValue();

            //get data
            crDisValueFrom.Value = dateTimePickerFrom.Value.ToString("dd/MM/yyyy");
            crDisValueTo.Value = dateTimePickerTo.Value.ToString("dd/MM/yyyy");
            pfd = cr.DataDefinition.ParameterFields;

            //set value
            crFrom = pfd["dateFrom"];
            crValues = crFrom.CurrentValues;
            crValues.Clear();
            crValues.Add(crDisValueFrom);
            crFrom.ApplyCurrentValues(crValues);

            crTo = pfd["dateTo"];
            crValues = crTo.CurrentValues;
            crValues.Clear();
            crValues.Add(crDisValueTo);
            crTo.ApplyCurrentValues(crValues);


            reportView.ReportSource = cr;
            reportView.Refresh();
        }

        private void frmBaoCaoGiaiNgan_Load(object sender, EventArgs e)
        {
            //set values for cb LDLINK
            AutoCompleteStringCollection autoCollection = new AutoCompleteStringCollection();
            AutoCompleteStringCollection autoCollection1 = new AutoCompleteStringCollection();
            string strQuery = "select distinct LD.[LD.LINK] as LINK from LD where LD.[LD.LINK]!='' ";
            DataTable dt = sql.getDataTable(strQuery);
            foreach(DataRow dr in dt.Rows)
            {
                autoCollection.Add(dr[0].ToString());
            }
            cbLDLINK.AutoCompleteCustomSource = autoCollection;
            cbLDLINK.DataSource = dt;
            cbLDLINK.ValueMember = "LINK";          

            
            //set values for cb Ma khach hang
            strQuery = "select distinct LD.[CUSTOMER.ID] as customerID from LD";
            dt = sql.getDataTable(strQuery);
            foreach (DataRow dr in dt.Rows)
            {
                autoCollection1.Add(dr[0].ToString());
            }
            cbMKH.AutoCompleteCustomSource = autoCollection1;
            cbMKH.DataSource = dt;
            cbMKH.ValueMember = "customerID";


            cbMKH.Text = "";
            cbLDLINK.Text = "";
        }
        
        public DataTable union(DataTable First, DataTable Second)
        {

            //Result table
            DataTable table = new DataTable("Union");

            //Build new columns
            DataColumn[] newcolumns = new DataColumn[First.Columns.Count];

            for (int i = 0; i < First.Columns.Count; i++)
            {
                newcolumns[i] = new DataColumn(
                First.Columns[i].ColumnName, First.Columns[i].DataType);
            }

            table.Columns.AddRange(newcolumns);
            table.BeginLoadData();

            foreach (DataRow row in First.Rows)
            {
                table.LoadDataRow(row.ItemArray, true);
            }

            foreach (DataRow row in Second.Rows)
            {
                table.LoadDataRow(row.ItemArray, true);
            }

            table.EndLoadData();
            return table;
        }
    }
}
